package com.food.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import com.food.dao.in.CollectDAO;
import com.food.model.Collect;

public class CollectDAOJdbc implements CollectDAO {
//	private static final String URL = "jdbc:sqlserver://192.168.34.108:1433;database=PJDB";
//	private static final String USER = "sa";
//	private static final String PASSWORD = "sa123456";
	private DataSource ds = null;
	public CollectDAOJdbc() {
		try {
			Context ctx = new InitialContext();
			this.ds = (DataSource) ctx.lookup("java:comp/env/jdbc/food");
		} catch (NamingException e) {
			e.printStackTrace();
		}
	}
	
	private static final String INSERT= 
			"insert into collect (mbid,rid) values(?,?)";
	@Override
	public void insertCollect(Collect bean) {
		Connection conn=null;
		PreparedStatement stmt=null;
		try {
//			conn = DriverManager.getConnection(URL, USER, PASSWORD);
			conn = this.ds.getConnection();
			stmt = conn.prepareStatement(INSERT);
			stmt.setInt(1, bean.getMbid());
			stmt.setInt(2, bean.getRid());
			stmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		finally{
			if (stmt!=null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn!=null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}
	private static final String DELECT=
			"delete from collect where collectid =?";
	@Override
	public void deleteCollect(int collectid) {
		Connection conn = null;
		PreparedStatement stmt = null;
		try {
//			conn = DriverManager.getConnection(URL, USER, PASSWORD);
			conn = this.ds.getConnection();
			stmt = conn.prepareStatement(DELECT);
			stmt.setInt(1, collectid);
			stmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		finally{
		if (stmt!=null) {
			try {
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (conn!=null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	  }
	}
	private static final String SELECTRECIPE =
			"select collectid, mbid,rid from collect where rid=?";
	@Override
	public List<Collect> findCollectByRecipe(int rid) {
		List<Collect> result = null;
		Collect foodrecipe = null;
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rest = null;
		try {
//			conn = DriverManager.getConnection(URL, USER, PASSWORD);
			conn = this.ds.getConnection();
			stmt = conn.prepareStatement(SELECTRECIPE);
			stmt.setInt(1, rid);
			rest = stmt.executeQuery();
			result=new ArrayList<Collect>();
			while(rest.next()){
				foodrecipe = new Collect();
				foodrecipe.setCollectid(rest.getInt("collectid"));
				foodrecipe.setMbid(rest.getInt("mbid"));
				foodrecipe.setRid(rest.getInt("rid"));
				result.add(foodrecipe);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
		if (rest!=null) {
			try {
				rest.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (stmt!=null) {
			try {
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (conn!=null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
		return result;
	}
	private static final String SELECTMB=
			"select collectid, mbid,rid from collect where mbid=?";
	@Override
	public List<Collect> findCollectByMb(int mbid) {
		List<Collect> result = null;
		Collect foodmb = null;
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rest = null;
		try {
//			conn = DriverManager.getConnection(URL, USER, PASSWORD);
			conn = this.ds.getConnection();
			stmt = conn.prepareStatement(SELECTMB);
			stmt.setInt(1, mbid);
			rest = stmt.executeQuery();
			result=new ArrayList<Collect>();
			while(rest.next()){
				foodmb = new Collect();
				foodmb.setCollectid(rest.getInt("collectid"));
				foodmb.setMbid(rest.getInt("mbid"));
				foodmb.setRid(rest.getInt("rid"));
				result.add(foodmb);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
		if (rest!=null) {
			try {
				rest.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (stmt!=null) {
			try {
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (conn!=null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
		return result;
	}
	public static void main(String args[]){
//		CollectDAOJdbc la = new CollectDAOJdbc();
//		Collect gr = new Collect();
//		gr.setRid(1);
//		gr.setMbid(2);
//		la.insertCollect(gr);
		
		
//		List<Collect> l = new ArrayList<Collect>();
//		List<Collect> r = new ArrayList<Collect>();
//		l = la.findCollectByMb(2);
//		r = la.findCollectByRecipe(1);
//		System.out.println(l);
//		System.out.println(r);
		
//		la.deleteCollect(2);
	}
}
